﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;

namespace Kwik.Orm
{
	/// <summary>
	/// Mapper class for System.Data.SqlClient connections
	/// </summary>
	public sealed class SqlClientSchema: BaseSchema
	{
		/// <summary>
		/// Create a new SqlClientMapper object
		/// </summary>
		public SqlClientSchema():base("", "System.Data.SqlClient") { }
		/// <summary>
		/// Create a new SqlClientMapper object with the supplied connectionstring
		/// </summary>
		/// <param name="connectionString"></param>
		public SqlClientSchema(string connectionString) : base(connectionString, "System.Data.SqlClient") { }

		/// <summary>
		/// Load the schema with the supplied connection
		/// </summary>
		/// <param name="conn">Connection to load the schema from</param>
		public void Load(SqlConnection conn)
		{
			Trace.WriteLine("Kwik.Orm.SqlClientMapper.Load: Loading MSSQL schema");
			base.Load(conn);
			SqlClientSchema.LoadDescriptions(conn, this);
			this.LoadColumns(conn);
			SqlClientSchema.LoadForeignKeys(conn, this);
		}

		/// <summary>
		/// Sets the connectionstring and loads the schema
		/// </summary>
		/// <param name="connectionString">Connectionstring to use to initialize a connection</param>
		public void Load(string connectionString)
		{
			this.ConnectionString = connectionString;
			this.Load();
		}

		/// <summary>
		/// Load the schema with current connectionstring
		/// </summary>
		public override void Load()
		{
			SqlConnection conn = new SqlConnection(this.ConnectionString);
			conn.Open();
			this.Load(conn);
			conn.Close();
			conn.Dispose();
		}

		/// <summary>
		/// Load column options
		/// </summary>
		/// <param name="conn"></param>
		private void LoadColumns(SqlConnection conn)
		{
			DataTable table = conn.GetSchema("Columns");
			foreach (DataRow row in table.Rows)
			{
				string tableName = Convert.ToString(row["TABLE_NAME"]);
				string columnName = Convert.ToString(row["COLUMN_NAME"]);
				if (this.Tables.ContainsKey(tableName) && this.Tables[tableName].Columns.ContainsKey(columnName))
				{
					string columnDefault = Convert.ToString(row["COLUMN_DEFAULT"]);
					this.Tables[tableName].Columns[columnName].DefaultValue = columnDefault.TrimStart('(').TrimEnd(')');

					Trace.WriteLine("Kwik.Orm.SqlClientMapper.LoadColumns: Default value for " + tableName + "." + columnName + " is '" + this.Tables[tableName].Columns[columnName].DefaultValue + "'");
				}
			}
		}

		/// <summary>
		/// loads descriptions from MSSQL using sys tables
		/// </summary>
		/// <param name="conn"></param>
		internal static void LoadDescriptions(DbConnection conn, ISchema mapper)
		{
			Trace.WriteLine("Kwik.Orm.SqlClientMapper.LoadDescriptions: Loading MSSQL column descriptions");

			DbCommand cmd = conn.CreateCommand();
			cmd.CommandText =
@"
select 
	t.name as [type], 
	t.name as [table], 
	c.name as [column], 
	p.value as description
from 
	sys.tables t, 
	sys.columns c, 
	sys.extended_properties p, 
	sys.types t2
where 
	p.major_id=c.object_id 
and 
	t.object_id=c.object_id
and 
	p.minor_id=c.column_id 
and 
	t2.system_type_id=c.system_type_id
;";

			cmd.Connection = conn;
			DbDataReader reader = cmd.ExecuteReader();

			while (reader.Read())
			{
				string tableName = reader.GetString(1);
				string columnName = reader.GetString(2);
				if (mapper.Tables.ContainsKey(tableName) && mapper.Tables[tableName].Columns.ContainsKey(columnName))
				{
					Trace.WriteLine("Kwik.Orm.SqlClientMapper.LoadDescriptions: Adding description for '" + tableName + "." + columnName + "'");
					mapper.Tables[tableName].Columns[columnName].Description = reader.GetString(3);
				}
			}
			reader.Close();
		}

		/// <summary>
		/// loads foreignkey constraints from MSSQL using sys tables
		/// </summary>
		/// <param name="conn"></param>
		protected static void LoadForeignKeys(DbConnection conn, ISchema mapper)
		{
			Trace.WriteLine("Kwik.Orm.SqlClientMapper.LoadForeignKeys: Loading MSSQL foreignkeys");

			DbCommand cmd = conn.CreateCommand();
			cmd.CommandText =
@"select
	f.name,
	f.delete_referential_action,
	f.update_referential_action,
	t1.name [table],
	t2.name [ref_table],
	c1.name [column],
	c2.name [ref_column]
from
	sys.foreign_keys f,
	sys.foreign_key_columns fc,
	sys.tables t1,
	sys.tables t2,
	sys.columns c1,
	sys.columns c2
where
	f.object_id=fc.constraint_object_id
and
	fc.parent_object_id=t1.object_id
and
	fc.referenced_object_id=t2.object_id
and
	c1.object_id=t1.object_id and c1.column_id=fc.parent_column_id
and
	c2.object_id=t2.object_id and c2.column_id=fc.referenced_column_id
and
	ISNULL(f.is_disabled,0)=0
;";

			cmd.Connection = conn;
			DbDataReader reader = cmd.ExecuteReader();

			while (reader.Read())
			{
				string constraintName = reader.GetString(0);
				byte deleteAction = reader.GetByte(1);
				byte updateAction = reader.GetByte(2);
				string tableName = reader.GetString(3);
				string refTableName = reader.GetString(4);
				string columnName = reader.GetString(5);
				string refColumnName = reader.GetString(6);

				if (mapper.Tables.ContainsKey(tableName)
					&& mapper.Tables[tableName].Columns.ContainsKey(columnName)
					&& mapper.Tables.ContainsKey(refTableName)
					&& mapper.Tables[refTableName].Columns.ContainsKey(refColumnName)
				)
				{
					Constraint constraint = new Constraint(constraintName);
					constraint.Type = ConstraintType.ForeignKey;

					constraint.Table = tableName;
					constraint.Column = columnName;
					constraint.ReferenceTable = refTableName;
					constraint.ReferenceColumn = refColumnName;
					constraint.UpdateAction = (ConstraintAction)updateAction;
					constraint.DeleteAction = (ConstraintAction)deleteAction;

					mapper.Tables[tableName].Constraints.Add(constraint.Name, constraint);

					Trace.WriteLine("Kwik.Orm.SqlClientMapper.LoadForeignKeys: Constraint '" + constraint.Name + "' added");
				}
			}
			reader.Close();
		}
	}
}
